﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;

namespace PhoneCard.DAL
{
    public partial class TuiUsersDal
    {
        public string ConnStr { set; get; }

        public List<Entities.TuiUsers> GetListUser(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM `tuiusers` ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            List<Entities.TuiUsers> list = new List<Entities.TuiUsers>();
            using (var connection = ConnectionFactory.GetOpenConnection(ConnStr))
            {
                list = connection.Query<Entities.TuiUsers>(strSql.ToString()).ToList();

            }
            return list;
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(Entities.TuiUsers model)
        {
            DynamicParameters Parameters = new DynamicParameters();
            int cnt = 0;
            string sQuery = "INSERT INTO tuiusers (userName,userPass,userType,company,detail,create_time,edit_time,pid,alipayUrl,taobaoUrl,linkMan,contactInfo,priceRatio)"
                              + " VALUES(@userName,@userPass,@userType,@company,@detail,@create_time,@edit_time,@pid,@alipayUrl,@taobaoUrl,@linkMan,@contactInfo,@priceRatio)";
            Parameters.Add("alipayUrl", model.alipayUrl);
            Parameters.Add("company", model.company);
            Parameters.Add("contactInfo", model.contactInfo);
            Parameters.Add("create_time", model.create_time);
            Parameters.Add("detail", model.detail);
            Parameters.Add("edit_time", model.edit_time);
            Parameters.Add("linkMan", model.linkMan);
            Parameters.Add("pid", model.pid);
            Parameters.Add("priceRatio", model.priceRatio);
            Parameters.Add("stateFlag", model.stateFlag);
            Parameters.Add("taobaoUrl", model.taobaoUrl);
            Parameters.Add("userName", model.userName);
            Parameters.Add("userPass", model.userPass);
            Parameters.Add("userType", model.userType);
            using (var connection = ConnectionFactory.GetOpenConnection(ConnStr))
            {

                cnt = connection.Execute(sQuery, Parameters);
            }

            if (cnt > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 根据ID删除一条数据
        /// </summary>
        public bool Delete(int id)
        {
            DynamicParameters Parameters = new DynamicParameters();
            int cnt = 0;
            string sQuery = "Delete FROM tuiusers " + "WHERE Id=@Id";
            Parameters.Add("Id", id);
            using (var connection = ConnectionFactory.GetOpenConnection(ConnStr))
            {
                cnt = connection.Execute(sQuery, Parameters);
            }
            if (cnt > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Entities.TuiUsers model)
        {
            DynamicParameters Parameters = new DynamicParameters();
            string sQuery = "UPDATE tuiusers SET userPass=@userPass,userType=@userType,company=@company,detail=@detail,edit_time=@edit_time,pid=@pid,alipayUrl=@alipayUrl,taobaoUrl=@taobaoUrl,linkMan=@linkMan,contactInfo=@contactInfo,priceRatio=@priceRatio where userName=@userName";
            Parameters.Add("alipayUrl", model.alipayUrl);
            Parameters.Add("company", model.company);
            Parameters.Add("contactInfo", model.contactInfo);
            Parameters.Add("create_time", model.create_time);
            Parameters.Add("detail", model.detail);
            Parameters.Add("edit_time", model.edit_time);
            Parameters.Add("linkMan", model.linkMan);
            Parameters.Add("pid", model.pid);
            Parameters.Add("priceRatio", model.priceRatio);
            Parameters.Add("stateFlag", model.stateFlag);
            Parameters.Add("taobaoUrl", model.taobaoUrl);
            Parameters.Add("userName", model.userName);
            Parameters.Add("userPass", model.userPass);
            Parameters.Add("userType", model.userType);

            int cnt = 0;
            using (var connection = ConnectionFactory.GetOpenConnection(ConnStr))
            {
                cnt = connection.Execute(sQuery, Parameters);
            }
            if (cnt > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 根据ID获取实体对象
        /// </summary>
        public Entities.TuiUsers GetModel(int id)
        {
            DynamicParameters Parameters = new DynamicParameters();
            string sQuery = "SELECT * FROM tuiusers " + "WHERE id = @id";
            Parameters.Add("id", id);
            using (var connection = ConnectionFactory.GetOpenConnection(ConnStr))
            {
                return connection.Query<Entities.TuiUsers>(sQuery, Parameters).FirstOrDefault();
            }
        }


        /// <summary>
        /// 根据userName获取实体对象
        /// </summary>
        public Entities.TuiUsers GetModelByUserName(string userName)
        {
            DynamicParameters Parameters = new DynamicParameters();
            string sQuery = "SELECT * FROM tuiusers " + "WHERE userName = @userName";
            Parameters.Add("userName", userName);


            using (var connection = ConnectionFactory.GetOpenConnection(ConnStr))
            {
                
                return connection.Query<Entities.TuiUsers>(sQuery, Parameters).FirstOrDefault();
            }
        }


        /// <summary>分页获取数据列表
        /// 
        /// </summary>
        public List<Entities.TuiUsers> GetListArray(string fileds, string orderstr, int PageSize, int PageIndex, string strWhere)
        {

            string cond = string.IsNullOrEmpty(strWhere) ? "" : string.Format(" where {0}", strWhere);

            string sql = string.Format("select {0} from `tuiusers` {1} order by {2} limit {3},{4}", fileds, cond, orderstr, (PageIndex - 1) * PageSize, PageSize);


            List<Entities.TuiUsers> list = new List<Entities.TuiUsers>();
            using (var connection = ConnectionFactory.GetOpenConnection(ConnStr))
            {
                list = connection.Query<Entities.TuiUsers>(sql).ToList();

            }
            return list;
        }



        /// <summary>计算记录数
        /// 
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        public int CalcCount(string where)
        {
            string sql = "select count(1) from `tuiusers`";
            if (!string.IsNullOrEmpty(where))
            {
                sql += " where " + where;
            }
            using (var connection = ConnectionFactory.GetOpenConnection(ConnStr))
            {
                int i = connection.QuerySingle<int>(sql);
                return i;

            }
        }



    }
}
